﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using DAL.SQLStatements;
using BAL.Entities;
using BAL.Utils;

namespace DAL.LogicComponents
{
    public class clsUserDAL
    {
        #region --- User Login and Check user Existed
        // This method going to validate user when they log into system
        public static clsUser queryUserLogin(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "colUserID, colCart, colFavorite";
            query.propWhere = "colUserName='" + obj.UserName + "' and colUserPassword='" + obj.Password + "' and colStatus=1";
            query.OrderByColumn = "colInsertDate";
            query.SortByDESC = true;
            try
            {   
                DataRow dr = query.getDatatable().Rows[0];
                int userID = Convert.ToInt32(dr["colUserID"]);
                if (userID > 0 && queryUpdateLastLogin(obj) == true)
                {
                    obj.UserID = userID;
                    obj.Card = dr["colCart"].ToString();
                    obj.Favorite = dr["colFavorite"].ToString();
                }
                    return obj;
            }
            catch
            {
                return obj;
            }
        }

        // This method going to check if user existed
        public static bool queryUserExisted(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "colUserName";
            query.propWhere = "colUserName='" + obj.UserName + "' and colStatus=1";
            return query.getBoolValue();
        }

        // This method going to update last login
        public static bool queryUpdateLastLogin(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colLastLogin", "'" + clsDateTimeSetting.UTCTime() + "'");
            return query.executeUpdate("tblUser", data, "colUserID=" + obj.UserID);
        }
        #endregion --- User Login and Check User Existed

        #region --- User Forget Their Password
        // This method going to check if email existed to execute send email method
        public static bool queryEmailExisted(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "colEmail";
            query.propWhere = "colEmail='" + obj.Email + "' and colStatus=1";
            return query.getBoolValue();
        }
        #endregion ----

        #region --- Get User List
        // This method going to get all user in database to display for Admin or Mod
        public static DataTable queryGetUserList()
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "colUserName, colPassword, colEmail, colAddress, colPhoneNumber, colCellPhone";
            query.propWhere = "colStatus=1";
            return query.getDatatable();
        }
        #endregion --- Get User List

        #region --- Get All User's
        public static DataTable queryGetAllUser()
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "*";
            query.propWhere = "colStatus = 1";

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Get User by ID
        public static DataTable queryGetUser(clsUser obj)
        {
            // Select all of products by user id
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "*";
            query.propWhere = "colStatus = 1 and colUserID = '" + obj.UserID + "'";

            // return result
            return query.getDatatable();
        }
        #endregion ---

        #region --- Get User Deleted List
        // This method going to get all deleted user in database to display for Admin or Mod
        #endregion --- Get User Deleted List

        #region --- Add New User
        // This method going to add new user
        public static bool queryUserRegister(clsUser obj)
        {
            if (queryUserExisted(obj) == false && queryEmailExisted(obj) == false)
            {   
                //Create new instance pf query builder
                clsQueryBuilder query = new clsQueryBuilder();

                // Add value to column(s)
                Dictionary<String, Object> data = new Dictionary<String, Object>();
                data.Add("colUserName", "'" + obj.UserName + "'");
                data.Add("colUserPassword", "'" + obj.Password + "'");
                data.Add("colEmail", "'" + obj.Email + "'");
                data.Add("colFirstName", "N'" + obj.FirstName + "'");
                data.Add("colLastName", "N'" + obj.LastName + "'");
                data.Add("colAddress", "N'" + obj.Address +"'");
                data.Add("colIdentityCard", obj.IdentityCard);
                data.Add("colCellPhone", obj.PhoneNumber);
                data.Add("colHomePhone", obj.HomePhone);
                data.Add("colCityID", obj.CityID);
                data.Add("colBirthday", "'" + obj.BirthDay + "'");
                data.Add("colLastLogin", "'" + clsDateTimeSetting.UTCTime() + "'");
                data.Add("colInsertDate", "'" + clsDateTimeSetting.UTCTime() + "'");
                data.Add("colAvatar", "'defaultAvatar.png'");
                data.Add("colStatus", 1);

                // Execute
                return query.executeInsertInto("tblUser", data);
            }
            else
            {
                return false;
            }
        }

        // This method going to check Enail existed
        public static bool queryCheckEmailExisted(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            query.propTable = "tblUser";
            query.propColumn = "colUserID";
            query.propWhere = "colEmail='" + obj.Email;
            return query.getBoolValue();
        }

        #endregion --- Add New user

        #region --- Update User Profile
        // This method going to update user profile
        public static bool queryUpdateUserProfile(clsUser obj)
        {
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            // Add value to column(s)
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colFirstName", "N'" + obj.FirstName + "'");
            data.Add("colLastName", "N'" + obj.LastName + "'");
            data.Add("colAddress", "N'" + obj.Address +"'");
            data.Add("colCellPhone","'"+ obj.PhoneNumber +"'");
            data.Add("colHomePhone", "'" + obj.HomePhone + "'");
            data.Add("colCityID", "'" + obj.CityID + "'");
            data.Add("colBirthday", "'"+ obj.BirthDay +"'");
            data.Add("colLastUpdate", "'"+ clsDateTimeSetting.UTCTime() + "'");

            // Condition
            string where = "colUserID ='" + obj.UserID + "'";

            // Execute query and return result
            return query.executeUpdate("tblUser", data, where);
        }
        #endregion --- Update User Profile

        #region --- Update User Cart List
        public static bool queryUpdateUserCartList(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("colCart", "'" + obj.Card + "'");

            // return result
            return query.executeUpdate("tblUser", data, "colUserID=" + obj.UserID);
        }
        #endregion ---

        #region --- Update User Favorite List
        public static bool queryUpdateUserFavoriteList(clsUser obj)
        {
            clsQueryBuilder query = new clsQueryBuilder();
            Dictionary<String, Object> data = new Dictionary<string, object>();
            data.Add("colFavorite", "'" + obj.Favorite + "'");

            // return result
            return query.executeUpdate("tblUser", data, "colUserID=" + obj.UserID);
        }
        #endregion ---

        #region --- User Update Their Avatar
        // This method going to update user avatar
        public static bool queryUpdateUserAvatar(clsUser obj)
        {
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            // Add value to column(s)
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colAvatar", obj.Avatar);
            data.Add("colLastUpdate", clsDateTimeSetting.UTCTime());

            // Condition
            string where = "colUserID ='" + obj.UserID + "'";

            // Execute query and return result
            return query.executeUpdate("tblUser", data, where);
        }
        #endregion --- Update User Profile

        #region --- User Deactive Their Account
        // This method going to deactive their account
        public static bool queryDeactiveAccount(clsUser obj)
        {
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            // Add value to column(s)
            Dictionary<String, Object> data = new Dictionary<String, Object>();
            data.Add("colIsDelete", 0);
            data.Add("colLastUpdate", clsDateTimeSetting.UTCTime());

            // Condition
            string where = "colUserID =" + obj.UserID;

            // Execute query and return result
            return query.executeUpdate("tblUser", data, where);
        }
        #endregion --- User Deactive Their Account

        #region --- User Edit Their Password
        // This method going to edit user's password
        public static bool queryEditUserPassword(clsUser obj)
        {   
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            if(queryCheckUserPassword(obj) == true)
            {
                Dictionary<String, Object> data = new Dictionary<String, Object>();
                data.Add("colUserPassword", "'" + obj.NewPassword + "'");
                return query.executeUpdate("tblUser", data, "colUserID=" + obj.UserID + "");
            }
            else
            { 
                return false;
            }
        }

        // This method going to check user's password
        public static bool queryCheckUserPassword(clsUser obj)
        {
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            query.propTable = "tblUser";
            query.propColumn = "colUserID";
            query.propWhere = "colUserID=" + obj.UserID + " and colUserPassword='" + obj.Password + "'";
            return query.getBoolValue();
        }
        #endregion

        #region --- Get User Profile
        // This method going to edit user's password
        public static DataRow queryGetUserProfile(clsUser obj)
        {
            //Create new instance of query builder
            clsQueryBuilder query = new clsQueryBuilder();

            query.propTable = "tblUser, tblCity as ci, tblCountry as co";
            query.propColumn = "colUserName, colFirstName, colLastName, colAvatar, colEmail, colAddress, colIdentityCard, colBirthday, colCellPhone, colHomePhone, colCityID, co.CountryID, colAvatar ";
            query.propWhere = "colCityID=ci.CityID and ci.CountryID= co.CountryID and colUserID=" + obj.UserID ;
            return query.getDatatable().Rows[0];
        }
        #endregion

        }
    }
